﻿Imports BusinessRule.DTO
Imports DatabaseAccess.DBUtility
Imports System.Text

Public Class uLMSKeyProcessBusiness
    Implements IBusiness.uLMSKeyProcessIBusiness
    ''' <summary>
    ''' 获取已经存在检测项的批次
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks>REPLACE(C.TOGETHERFLAG,C.PRODUCTNAME||'/','') TOGETHERFLAGNAME, modify by tianFW 2018年5月17日 单件单卡取C.TOGETHERFLAG 数量批取C.PRODUCTNAME</remarks>
    Protected Function GetCheckedPonitInfo(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements IBusiness.uLMSKeyProcessIBusiness.GetCheckedPonitInfo
        Dim strSelect As String =
            <string>
             SELECT C.PRODUCTNAME,C.PRODUCTID,C.WORKFLOWSTEPNAME,C.WORKFLOWSTEPID,C.SPECNO, C.WORKFLOWID,C.WORKFLOWSTEPSEQUENCE AS SEQUENCE,C.TOGETHERFLAG,
             C.CONTAINERTYPE,DECODE(C.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,C.MFGORDERNAME,C.PLANQTY,
             C.PLANNEDSTARTDATE,C.PLANNEDCOMPLETIONDATE,C.WORKCENTERNAME,C.WORKCENTERID,C.FACTORYID,C.PRODUCTDESCRIPTION,C.PRODUCTREVISION,C.ISSELFMFG,
            </string>
        Dim sb As New StringBuilder
        Dim sbCondition As String = GetCondition(para)
        sb.AppendLine(strSelect)
        sb.AppendLine("REPLACE(C.TOGETHERFLAG,C.PRODUCTNAME||'/','') TOGETHERFLAGNAME,")
        sb.AppendLine("SUM(C.QTY) AS WIPQTY ,'' CONTAINERID,''CONTAINERNAME,NULL WORKREPORTNUM,'' FaContianerID,NULL FIRSTCHECKNUM,NULL STEPCHECKNUM,''ISKEY,''SPECNAME,''SPECREVISION,''PARENTSPECNO")
        sb.AppendLine(",NVL(C.NONCONFORMINGNUM,0) NONCONFORMINGNUM FROM CONTAINERCURRENTINFO C ")
        sb.AppendLine(" left JOIN stepcheckpoint sc ON c.productid=sc.productid AND sc.workflowstepid=c.workflowstepid")
        sb.AppendLine(sbCondition)
        sb.AppendLine(" AND C.CONTAINERTYPE='7'")
        sb.AppendLine(" GROUP BY C.PRODUCTID, C.PRODUCTNAME, C.WORKFLOWSTEPNAME,C.WORKFLOWSTEPID, C.WORKFLOWID, C.WORKFLOWSTEPSEQUENCE, C.SPECNO,")
        sb.AppendLine(" C.TOGETHERFLAG, C.CONTAINERTYPE,C.MFGORDERNAME,C.PLANQTY, C.PLANNEDSTARTDATE, C.PLANNEDCOMPLETIONDATE,")
        sb.AppendLine(" C.WORKCENTERNAME,C.WORKCENTERID,C.FACTORYID,C.PRODUCTDESCRIPTION,C.PRODUCTREVISION,C.NONCONFORMINGNUM,C.ISSELFMFG")
        sb.AppendLine(" UNION ")
        sb.AppendLine(strSelect)
        sb.AppendLine("SUBSTR(C.CONTAINERNAME, LENGTH(C.PRODUCTNAME) + 2) TOGETHERFLAGNAME,")
        sb.AppendLine("C.QTY AS WIPQTY , C.CONTAINERID,C.CONTAINERNAME,C.WORKREPORTNUM,C.CONTAINERID AS FaContianerID,C.FIRSTCHECKNUM,C.STEPCHECKNUM,C.ISKEY,C.SPECNAME,C.SPECREVISION,C.PARENTSPECNO")
        sb.AppendLine(",NVL(C.NONCONFORMINGNUM,0) NONCONFORMINGNUM FROM CONTAINERCURRENTINFO C ")
        sb.AppendLine(" left JOIN stepcheckpoint sc ON c.productid=sc.productid AND sc.workflowstepid=c.workflowstepid")
        sb.AppendLine(sbCondition)
        sb.AppendLine(" AND C.CONTAINERTYPE<>'7' ")
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        sbCondition = String.Format("SELECT C.* FROM CONTAINERCURRENTINFO C left JOIN stepcheckpointresult scr ON scr.containerid=c.containerid {0} ", sbCondition) '将视图更换为新表 tianfw2017-06-05
        Return DatabaseHelper.GetContainerForPage(paraPage, sbCondition)
    End Function


    ''' <summary>
    ''' 创建批次查询数据条件
    ''' Create:YangSJ
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetCondition(ByVal para As Dictionary(Of String, String)) As String
        Dim sbCondition As New StringBuilder
        sbCondition.AppendFormat(" WHERE C.FACTORYID='{0}' ", para("FactoryID"))

        If para.Keys.Contains("ProductName") Then
            sbCondition.AppendFormat(" AND C.PRODUCTNAME LIKE'%{0}%'", para("ProductName"))
        End If
        If para.Keys.Contains("StepNo") Then
            sbCondition.AppendFormat(" AND C.SPECNO LIKE'%{0}%'", para("StepNo"))
        End If
        If para.Keys.Contains("ContainerName") Then
            sbCondition.AppendFormat(" AND C.CONTAINERNAME LIKE '{0}%'", para("ContainerName"))
        End If
        If para.Keys.Contains("MfgOrderName") Then 'add:Wangjh 2017-8-8
            sbCondition.AppendFormat(" AND C.MfgOrderName LIKE '{0}%'", para("MfgOrderName"))
        End If
        If para("PrintType") = "0" Then
            sbCondition.AppendLine(" and sc.KeyCheck=1 and C.CONTAINERTYPE<>'7'")
        End If
        GetCondition = sbCondition.ToString
    End Function

    ''' <summary>
    ''' 获取跟踪卡打印信息
    ''' </summary>
    ''' <returns></returns>
    Private Function GetContainerPrintInfo(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements IBusiness.uLMSKeyProcessIBusiness.GetContainerPrintInfo
        Dim ds As New DataSet
        Dim strSQL As String = ""
        Dim workflowID As String = ""
        Dim result As New uMESProcessResult

        strSQL = GetContainerMain(QueryCondition)
        Dim dtMain As DataTable = DatabaseHelper.Query_ReturnDatTable(strSQL)
        dtMain.TableName = "dtMain"
        ds.Tables.Add(dtMain)

        '批次当前工序的工艺路线
            strSQL = String.Format(" SELECT cci.workflowid FROM containercurrentinfo cci WHERE cci.containerid='{0}'", QueryCondition("ContainerID").ToString.Trim)
            workflowID = DatabaseHelper.Query_ReturnDatTable(strSQL).Rows(0)("workflowID")
        '批次对应零件对应的历史加工工序

        '批次所有报工记录
        strSQL = GetContainerReport(QueryCondition)
        Dim dtReportStep As DataTable = DatabaseHelper.Query_ReturnDatTable(strSQL)
        If dtReportStep.Rows.Count = 0 Then '如果没有报工记录则查询所有的检测项信息
            strSQL = GetCheckPointInfoByContainerID(QueryCondition)
            dtReportStep = DatabaseHelper.Query_ReturnDatTable(strSQL)
        End If
        dtReportStep.TableName = "dtReportStep"
        ds.Tables.Add(dtReportStep)

        result.DBDataSet = ds
        result.Result = True
        Return result
    End Function
    ''' <summary>
    ''' 获取批次零件信息SQL
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetContainerMain(ByVal Query As Dictionary(Of String, String)) As String

        'Dim sql = " SELECT csr.workcentername,csr.productname,csr.productid,csr.productdesc FROM containersteprecord csr "

        'sql += String.Format("WHERE csr.containerid='{0}'", Query("ContainerID").Trim)
        'sql += " GROUP BY csr.workcentername,csr.productname,csr.productid,csr.productdesc"

        Dim strSql = " SELECT wc.workcentername,pb.productname,c.productid,p.description productdesc FROM container c " &
                      "  LEFT JOIN workcenter wc ON wc.workcenterid=c.workcenterid" &
                      "  LEFT JOIN product p ON p.productid=c.productid" &
                       " LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid"
        strSql += String.Format(" WHERE c.containerid='{0}'", Query("ContainerID").Trim)
        strSql += "  GROUP BY wc.workcentername,pb.productname,c.productid,p.description"
        GetContainerMain = strSql
    End Function

    ''' <summary>
    ''' 获取批次所有报工信息SQL
    ''' </summary>
    ''' <param name="Query"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetContainerReport(ByVal Query As Dictionary(Of String, String)) As String
        Dim sql As String = <string>
                               SELECT csr.reportdate,csr.containername,csr.specno,csr.dispatchresourcename,csr.reportqty,csr.passqty,csr.lossqty,csr.defectqty,
                                csr.reportemployeename,csr.checkemployeename,scp.checkpointnote,scpr.checkpointresultnote FROM containersteprecord csr
                                LEFT JOIN stepcheckpoint scp ON scp.productid=csr.productid AND scp.workflowstepid=csr.workflowstepid
								LEFT JOIN stepcheckpointresult scpr ON scpr.stepcheckpointid=scp.stepcheckpointid  AND scpr.containerid=csr.containerid                               
                           </string>

        sql += String.Format("WHERE csr.containerid='{0}'", Query("ContainerID").Trim)
        sql += " order by csr.specno"
        GetContainerReport = sql
    End Function
    ''' <summary>
    ''' 获取批次所有检测项信息
    ''' </summary>
    ''' <param name="Query"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetCheckPointInfoByContainerID(ByVal Query As Dictionary(Of String, String)) As String
        Dim strSql As String = " SELECT NULL reportdate,c.containername,s.specno,NULL dispatchresourcename,NULL reportqty,NULL passqty,NULL lossqty,NULL defectqty,NULL reportemployeename,NULL checkemployeename,sc.checkpointnote,NULL checkpointresultnote FROM stepcheckpoint sc " &
                               " LEFT JOIN Workflowstep ws ON ws.workflowstepid=sc.workflowstepid" &
                              "  LEFT JOIN specbase sb ON sb.specbaseid=ws.specbaseid" &
                               " LEFT JOIN spec s ON s.specid=NVL(sb.revofrcdid,ws.specid)" &
                               " LEFT JOIN container c ON c.productid=sc.productid"
        strSql += String.Format(" WHERE c.containerid='{0}'", Query("ContainerID").Trim)
        strSql += " ORDER BY sc.intime"

        GetCheckPointInfoByContainerID = strSql
    End Function
End Class
